﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model;
using System.Data;

namespace DAL
{
    public class HouseHolderService
    {
        public static HouseHolder AddHouseHolder(HouseHolder hHolder)
        {
            string sql = string.Format("insert into HouseHolder(PersonalInformationId,EvaluationScore,IsRecommended,IsChecked) values({0},{1},{2},{3});select @@IDENTITY;", 
                hHolder.PersonalInformationId,
                hHolder.EvaluationScore,
                hHolder.IsRecommended,
                hHolder.IsChecked);

            int id = DBHelper.GetScalar(sql);
            return GetHouseHolderById(id);
        }

        public static HouseHolder GetHouseHolderById(int id)
        {
            string sql = string.Format("select * from HouseHolder where Id={0}", id);
            return GetHouseHolderBySql(sql);
        }

        private static HouseHolder GetHouseHolderBySql(string sql)
        {
            List<HouseHolder> list = GetHouseHoldersBySql(sql);
            if (list.Count() > 0)
                return list[0];
            return null;
        }

        private static List<HouseHolder> GetHouseHoldersBySql(string sql)
        {
            List<HouseHolder> list = new List<HouseHolder>();
            DataTable dt = DBHelper.GetDataSet(sql);
            foreach (DataRow row in dt.Rows)
            {
                int result=0;
                HouseHolder hHolder = new HouseHolder();
                hHolder.Id = (int)row["Id"];

                Int32.TryParse(row["PersonalInformationId"].ToString(), out result);
                hHolder.PersonalInformationId = result;

                Int32.TryParse(row["IsRecommended"].ToString(), out result);
                hHolder.IsRecommended = result;

                Int32.TryParse(row["EvaluationScore"].ToString(), out result);
                hHolder.EvaluationScore = result;

                Int32.TryParse(row["IsChecked"].ToString(), out result);
                hHolder.IsChecked = result;

                list.Add(hHolder);
            }
            return list;
        }

        public static int GetPersonalInformationIdById(int id)
        {
            HouseHolder hHolder = GetHouseHolderById(id);
            if (hHolder != null)
                return hHolder.PersonalInformationId;
            else
                return -1;
        }

        public static List<HouseHolder> GetHouseHoldersChecked()
        {
            string sql = "SELECT * FROM HOUSEHOLDER WHERE IsChecked=1 ORDER BY Id DESC";
            return GetHouseHoldersBySql(sql);
        }

        public static List<HouseHolder> GetHouseHoldersNotChecked()
        {
            string sql = "SELECT * FROM HOUSEHOLDER WHERE IsChecked=0 ORDER BY Id DESC";
            return GetHouseHoldersBySql(sql);
        }

        public static HouseHolder UpdateHouseHolder(HouseHolder hHolder)
        {
            string sql = string.Format("update HouseHolder set PersonalInformationId={0},EvaluationScore={1},IsRecommended={2},IsChecked={3} where Id={4}",
                hHolder.PersonalInformationId, hHolder.EvaluationScore, hHolder.IsRecommended, hHolder.IsChecked, hHolder.Id);
            DBHelper.ExecuteCommand(sql);
            return GetHouseHolderById(hHolder.Id);
        }

        public static List<HouseHolder> GetAllHouseHolders()
        {
            string sql = string.Format("select * from HouseHolder order by id asc");
            return GetHouseHoldersBySql(sql);
        }


        public static HouseHolder GetHouseHolderByPersonalInformationId(int pId)
        {
            string sql = string.Format("select * from HouseHolder where PersonalInformationId={0}", pId);
            return GetHouseHolderBySql(sql);
        }

        public static bool HasId(int id)
        {
            if (GetHouseHolderById(id) == null)
                return false;
            else
                return true;
        }
    }
}
